<?php
namespace app\index\controller;
use think\Controller;
use think\Db;
use think\Url;
use app\index\model\Menu;
use app\index\model\prodUser;
use app\index\model\Test;
use app\index\model\User;
use app\index\model\authRule;
use app\index\model\Region;
use app\index\model\shippingArea;

class Sql extends Controller
{
    public function index(){

    	//键值对查询(单字段)
    	// $list = Db::name('menu')
    	// 	->where('level',1)
    	// 	->column('name','id');


    	//所有
    	// $list = Db::name('menu')
    	// 	->where('level',1)
    	// 	->column('*','id');

    	//聚合查询
    	// $count = Db::name('menu')->where('level',1)->count();

    	//menu 最大的id
    	// $max = Db::name('menu')->where('level',1)->max('id');
    	
    	//原生sql 简单查询 (占位符)
    	// $result = Db::name('menu')
    	// 	->where("id < :id and name like :name",['id' => 150	,'name'=> "%小米%"])
    	// 	->select();

    	//自动转换时间戳 whereTime
   //  	$result = Db::name('auth_rule')
			// 	//本周 'this week' , 最近两天 '-2 days'
   //  		// ->whereTime('create_time','>','2016-1-1')
   //  			//??BUG??
   //  		// ->whereTime('create_time','between',['2015-1-1','2017-1-1'])
   //  			//yestoday week last week
			// // ->whereTime('create_time','today')
   //  		->select();

    	//分块查询 解决服务器压力 原理 用id大于 判断 返回值bool
    	//原始方法
    	// Db::name('auth_rule')
    	// 	->whereTime('create_time','>','2016-1-1')
    	// 	->chunk(3,function($list){
    	// 		foreach ($list as $lk => $lv) {
    	// 			foreach ($lv as $k => $v) {
    	// 				echo $k.'=>'.$v.'<br/><br/>';
    	// 			}
    	// 		}
    	// 	});


    	// p($result);
    }


    public function testModel(){
        // $result = Menu::get(105);
            //在[data:protected]里

        //这个格式对应表prodUser
        // $result = prodUser::get(118);
        
        //INSERT
        //原理：会show一下先判断字段是否存在,※※※??BUG??存在执行update反之执行insert※※※
        // $test = test::get(1);
        // $test = new test();
        // var_dump($test);


        // $test->id = 103;
        // $test->keyCount = 4; 
        // $test->save();
        //不想更新:
        // $test->isUpdate(false)->save();


        //方法2 
        // $add['id'] = '100';
        // $add['keyCount'] = '1';
        // $result = test::create($add);

        // $test = new test();
        // $list = [
        //     ['keyCount'=>'231'],
        //     ['keyCount'=>'232'],
        // ];
        //※※※?BUG?带上id(不重复)一起insert就会变成update?※※※
        //修改save()
        // $result = $test->saveAll($list);


        //连查带改
        // $test = Test::get(1);
        // // echo $test->keyCount;

        // //因为ArrayAccess接口 可以将类的属性(对象)当做数组来访问
        // echo $test['keyCount'];
        // $test->keyCount = '777';
        // $result = $test->save();


        //条件查询(省去简单where)
        // $result = test::getByKeycount('666');

        //多条件查询
        // $result = test::all();
        // $result = test::all(['id'=>100]);


        //update
        // $data['keyCount'] = '1314';
        // test::update($data,['id'=>100]);


        //delete
        // test::destroy(100);
        // p($result);
        
        
        // $menu = Menu::get(105);
        // echo $menu->name = '2017-9-12';
        // $menu->save();
      
        //※※※?BUG?※※※
        // $rule = authRule::get();
        // $rule->create_time = '1496819156';
        // $rule->isUpdate(false)->save();

        //修改
        // $rule = authRule::get(18);
        // // echo $rule->create_time = '2012-12-1';
        // // $rule->name = ['aa'=>11];
        // p($rule->name);                                   
        // $rule->save();
         
        // $rule = authRule::get(10);
        /**
         * 插入以后这么插
         * @var authRule
         */
        // $rule = new authRule();
        // $data = ['name'=>'testM/testController','title'=>'1','type'=>'1','condition'=>' ','pid'=>'1','sort'=>'1','create_time'=>'1','update_time'=>'2','update_time'=>' ','remark'=>' '];
        // $rule::create($data);
    
        //更改/自动转换
        // $rule = authRule::get(25);
        // // $data = ['name'=>'testM/testController','title'=>'1','type'=>'1','condition'=>' ','pid'=>'1','sort'=>'1','create_time'=>'1','update_time'=>'2','update_time'=>' ','remark'=>' '];
        // $rule->create_time = '2001-9-12';
        // $rule->save();

        // 范围查询
        // $result = Menu::scope('level,top')->all();

        // $user = User::get();

        // p($user->username);

        // foreach ($user->comm as $comm) {
        //     p($comm);
        // }

        //关联过滤查询
        // $user = User::get(2);
        // $comment = $user->comm()->where('keyCount',1234)->select();

        // foreach ($comment as $comm) {
        //     echo $comm->keyCount.'<br/>';
        // }

        // $comm  = $user->comm()->getByKeycount('1234');
           // echo $comm; 

        //终极简写
        // $user = User::get(2,'comm');
        // p($user);


        //查询有评论过的用户列表
        // $user = User::has('comm')->select();
        //Execute SELECT * FROM `user` `a` JOIN `comment` `b` ON `a`.`user_id`=`b`.`uid` GROUP BY b.uid HAVING COUNT(*)>=1 
        
        // $user = User::has('comm','>=',2)->select();
        //Execute SELECT * FROM `user` `a` JOIN `comment` `b` ON `a`.`user_id`=`b`.`uid` GROUP BY b.uid HAVING COUNT(*)>=2 

        // $user = User::hasWhere('comm',['keyCount'=>'5678'])->select();
        //Execute SELECT `a`.* FROM `user` `a` JOIN `comment` `b` ON `a`.`user_id`=`b`.`uid` WHERE `b`.`keyCount` = '5678' 
        

        // 关联更新 

        // $user = User::get(1);
        // $comm = $user->comm()->getByKeycount('10086');
        // //getByXXX <=>where("XXX=");
        // $comm->keyCount = '1008611';
        // $comm->Other = '1008611';
        // $comm->save();

        //关联查询update方法更新
        // $user = User::get(1);
        // $user->comm()->where('Other','1008611')->update(['keyCount'=>'666']);


        // 删除部分关联数据
       // $user = User::get(1);
       // // 删除部分关联数据
       // $comm = $user->comm()->getByKeycount('666');
       // $comm->delete(); 

        // 轿车  一对一案例
            //$table1->teble2Key = 'value';
        // $user = User::get(1);
        // echo $user->car->keyCount;
        // 新增用户 关联 汽车

        // $user = new User;
        // $user->username = 'hello@1243.com';
        // $user->password = 'TPshop';
        // if($user->save()){
        //    $car['keyCount'] = '12345678';
        //    //uid 不指定
        //    $user->car()->save($car); // Relation对象  添加一部车
        //    return '用户[ ' . $user->username . ' ]新增成功';
        // } else {
        //    return $user->getError();
        // }

        // 关联更新
        //$->k->v;
        /**
         * 这个k不是key而是模型里定义的!!方法名!!
         */
        // $user = User::get(1);
        // $user->username = '修改';
        // if ($user->save()) {
        //     // 更新关联数据
        //    $user->car->keyCount = '666666';
        //    $user->car->save();
        //    return '用户[ ' . $user->username . ' ]更新成功';
        // } else {
        //     return $user->getError();
        // }

        // $user = User::get(1);
        // if ($user->delete()) {
        //    // 删除关联数据
        //    $user->car->delete();
        //    return '用户[ ' . $user->username . ' ]删除成功';
        // } else {
        //    return $user->getError();
        // }  

        //枢纽表(中间表)
        
        // 关联新增     

        //给某个地区增加编辑配送区域，并且由于这个配送区域还没创建过，所以可以使用下面的方式：
        // $region = Region::getByName('北京市');
        // // 新增配送区域 并自动写入枢纽表
        // $region->shippingArea()->save(['shipping_area_name' => '中国首都']);


        //给当前用户新增多个用户角色
        //给深圳(id=4)の关联表 加字段
        $region = Region::get(4);
        $region->shippingArea()->saveAll([
            ['shipping_area_name' => '深圳新增字段1'],
            ['shipping_area_name' => '深圳新增字段2'],
        ]);  

        // p($result);  
    }

}
